/* -- Visit the following site to download SVG maps for countries --------
https://www.amcharts.com/svg-maps/
http://mapsvg.com/maps/
http://mapsvg.com/maps/geo-calibrated/china.svg
--------------------------------------------------------------------------*/

Use MyDB;
GO

/* --- Manually ---- */
UPDATE Country
SET Map=(SELECT CONVERT(XML, BulkColumn) AS SVGMap 
FROM OPENROWSET(BULK 'C:\Maps\my.svg', SINGLE_BLOB) AS x)
WHERE Code='my';

/* --- Dynamic SQL ---- */
DECLARE @code AS char(2) = 'sa';
DECLARE @path AS varchar(128) = 'C:\Maps\'+@code+'.svg';
DECLARE @sql AS varchar(256) = 'UPDATE Country SET Map=(SELECT CONVERT(XML, BulkColumn) AS SVGMap  FROM OPENROWSET(BULK ''' + @path + ''', SINGLE_BLOB) AS x) WHERE Code='''+@code+'''';
EXEC (@sql)

--DROP IF EXISTS PROCEDURE AddMap;
/* Create Stroed Procedure to Add Map */
CREATE PROCEDURE [AddMap] 
	@code AS char(2) 
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @path AS varchar(128) = 'C:\Maps\'+@code+'.svg';
	DECLARE @sql AS varchar(256) = 'UPDATE Country SET Map=(SELECT CONVERT(XML, BulkColumn) AS SVGMap  FROM OPENROWSET(BULK ''' + @path + ''', SINGLE_BLOB) AS x) WHERE Code='''+@code+'''';
	EXEC (@sql)	
END

CREATE PROCEDURE [ReadMap] 
	@code AS char(2), 
	@map AS nvarchar(MAX) OUTPUT 
AS
BEGIN
	SET NOCOUNT ON;
	SET @map = CONVERT(nvarchar(MAX), (SELECT Map FROM Country WHERE Code=@code));
END

/* Call Stored Procedure to Add Map */
EXEC dbo.AddMap @code='jp';



/* Call Stored Procedure to Retrieve Map */
DECLARE @resultMap AS nvarchar(MAX);
EXECUTE [ReadMap] 'jp', @resultMap OUTPUT

SELECT @resultMap As Map;
GO


/* Retrieve the Map */
SELECT Map FROM Country WHERE Code='jp';

/* Clear All Maps */
UPDATE Country
SET Map=NULL;





